package com.wuliu.business.sys;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import com.wuliu.model.sys.User;
import com.wuliu.model.sys.Menu;
import com.wuliu.util.DbPool;
import com.wuliu.util.DbUtil;

public class MenuObj {
	public static Menu getMenuById(String id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Menu menu = new Menu();
		if(id==null || id.equals(""))
			return menu;
		String strsql = "SELECT * FROM WL_MENU WHERE MENU_ID=?";
		try{
			conn = DbPool.getConnection();
			pstmt = conn.prepareStatement(strsql);
			pstmt.setString(1,id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				menu.setMenuId(rs.getString("MENU_ID"));
				menu.setMenuName(rs.getString("MENU_NAME"));
				menu.setMenuRequest(rs.getString("MENU_REQUEST"));
				menu.setParentId(rs.getString("PARENT_ID"));
				menu.setMenuImg(rs.getString("MENU_IMG"));
				menu.setMenuLevel(rs.getString("MENU_LEVEL"));
				menu.setAddBy(rs.getString("ADD_BY"));
				menu.setAddDate(rs.getString("ADD_DATE"));
				menu.setAddTime(rs.getString("ADD_TIME"));
				menu.setSortNum(rs.getString("SORT_NUM"));
				menu.setActiveFlag(rs.getString("ACTIVE_FLAG"));
			}
		}catch(SQLException sqlE){
			sqlE.printStackTrace();
		}finally{
			DbPool.close(conn, pstmt, rs);
		}
		return menu;
	}
	public int addMenu(Menu menu){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;		
		String sql = " INSERT INTO WL_MENU (MENU_ID,MENU_NAME,MENU_REQUEST,PARENT_ID,MENU_IMG," +
					 " MENU_LEVEL,ADD_BY, ADD_DATE,ADD_TIME,SORT_NUM,ACTIVE_FLAG)" +
                     " VALUES(?,?,?,?,?,(SELECT MENU_LEVEL FROM (SELECT MENU_LEVEL FROM WL_MENU WHERE MENU_ID=? ) AS MENU_LEVEL)+1,?,DATE_FORMAT(NOW(),'%Y%m%d') ,DATE_FORMAT(NOW(),'%H%i%s'),?,?) ";
		
		try{
			conn = DbPool.getConnection();
			pstmt = conn.prepareStatement(sql);	
			pstmt.setString(1, UUID.randomUUID().toString());
			pstmt.setString(2, menu.getMenuName());
			pstmt.setString(3, menu.getMenuRequest());
			pstmt.setString(4, menu.getParentId());
			pstmt.setString(5, menu.getMenuImg());
			pstmt.setString(6, menu.getParentId());
			pstmt.setString(7, menu.getAddBy());
			pstmt.setString(8, menu.getSortNum()); 
			pstmt.setString(9, menu.getActiveFlag()); 

			result = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DbPool.close(conn, pstmt, rs);
		}
		return result;
	}
	
	
	public int updateMenu(Menu menu){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;		
		String sql = "  UPDATE WL_MENU SET MENU_NAME = ?,MENU_REQUEST=?,MENU_IMG=?,SORT_NUM=?,UPD_BY=?,UPD_DATE=DATE_FORMAT(NOW(),'%Y%m%d'),UPD_TIME=DATE_FORMAT(NOW(),'%H%i%s') WHERE MENU_ID =?   ";
		try{
			conn = DbPool.getConnection();
			pstmt = conn.prepareStatement(sql);			
			pstmt.setString(1, menu.getMenuName());
			pstmt.setString(2, menu.getMenuRequest());
			pstmt.setString(3, menu.getMenuImg());
			pstmt.setString(4, menu.getSortNum());
			pstmt.setString(5, menu.getUpdBy());
			pstmt.setString(6, menu.getMenuId()); 
			result = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DbPool.close(conn, pstmt, rs);
		}
		return result;
	}
	
	public String deleteMenu(String menuId){
		if(menuId.equals("201611100001"))
			return "不能删除根菜单";
		String roleId = "SELECT ROLE_ID FROM WL_ROLE_MENU WHERE MENU_ID='"+menuId+"' ";
		roleId = DbUtil.getQueryFiledValue(roleId, "ROLE_ID");
		String msg = "";
		if(roleId==null||roleId.equals("")){
			 StringBuffer sb = new StringBuffer();
		     sb.append(" DELETE FROM WL_MENU WHERE MENU_ID = '"+menuId+"' ");
		     List<?> list = new ArrayList();
		     boolean i = DbUtil.execute(sb.toString());
		     if (i==true) {
		         msg = "删除成功!";
		     }else{
		    	 msg = "删除失败!";
		     }
		}else{
			msg =  "删除错误；可能是该菜单已与相应角色绑定";
		}
	     return msg;
	}
	public static List<Menu> getMenuList(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Menu> list = new ArrayList<Menu>();
		String strsql = "SELECT * FROM WL_MENU ";
		try{
			conn = DbPool.getConnection();
			pstmt = conn.prepareStatement(strsql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Menu menu = new Menu();
				menu.setMenuId(rs.getString("MENU_ID"));
				menu.setMenuName(rs.getString("MENU_NAME"));
				menu.setMenuRequest(rs.getString("MENU_REQUEST"));
				menu.setParentId(rs.getString("PARENT_ID"));
				menu.setMenuImg(rs.getString("MENU_IMG"));
				menu.setMenuLevel(rs.getString("MENU_LEVEL"));
				menu.setAddBy(rs.getString("ADD_BY"));
				menu.setAddDate(rs.getString("ADD_DATE"));
				menu.setAddTime(rs.getString("ADD_TIME"));
				menu.setSortNum(rs.getString("SORT_NUM"));
				menu.setActiveFlag(rs.getString("ACTIVE_FLAG"));
				list.add(menu);
			}
		}catch(SQLException sqlE){
			sqlE.printStackTrace();
		}finally{
			DbPool.close(conn, pstmt, rs);
		}
		return list;
	}
	public static List<Menu> getMenuListByRole(String id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Menu> list = new ArrayList<Menu>();
		if(id==null || id.equals(""))
			return list;
		String strsql = "SELECT * FROM WL_MENU WHERE MENU_ID IN " +
				"(SELECT MENU_ID FROM WL_ROLE_MENU WHERE ROLE_ID=?)";
		try{
			conn = DbPool.getConnection();
			pstmt = conn.prepareStatement(strsql);
			pstmt.setString(1,id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Menu menu = new Menu();
				menu.setMenuId(rs.getString("MENU_ID"));
				menu.setMenuName(rs.getString("MENU_NAME"));
				menu.setMenuRequest(rs.getString("MENU_REQUEST"));
				menu.setParentId(rs.getString("PARENT_ID"));
				menu.setMenuImg(rs.getString("MENU_IMG"));
				menu.setMenuLevel(rs.getString("MENU_LEVEL"));
				menu.setAddBy(rs.getString("ADD_BY"));
				menu.setAddDate(rs.getString("ADD_DATE"));
				menu.setAddTime(rs.getString("ADD_TIME"));
				menu.setSortNum(rs.getString("SORT_NUM"));
				menu.setActiveFlag(rs.getString("ACTIVE_FLAG"));
				list.add(menu);
			}
		}catch(SQLException sqlE){
			sqlE.printStackTrace();
		}finally{
			DbPool.close(conn, pstmt, rs);
		}
		return list;
	}
	
	public static List<Menu> getMenuListByAccount(String parentId,String userAccount){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Menu> list = new ArrayList<Menu>();
		if(userAccount==null || userAccount.equals(""))
			return list;
		String strsql = "SELECT DISTINCT M.* FROM WL_MENU M,WL_ROLE_MENU RM,WL_USER_ROLE UR" +
				" WHERE M.MENU_ID=RM.MENU_ID AND RM.ROLE_ID=UR.ROLE_ID " +
				" AND UR.USER_ACCOUNT=? AND M.PARENT_ID=? ORDER BY M.SORT_NUM ";
		try{
			conn = DbPool.getConnection();
			pstmt = conn.prepareStatement(strsql);
			pstmt.setString(1,userAccount);
			pstmt.setString(2,parentId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Menu menu = new Menu();
				menu.setMenuId(rs.getString("MENU_ID"));
				menu.setMenuName(rs.getString("MENU_NAME"));
				menu.setMenuRequest(rs.getString("MENU_REQUEST"));
				menu.setParentId(rs.getString("PARENT_ID"));
				menu.setMenuImg(rs.getString("MENU_IMG"));
				menu.setMenuLevel(rs.getString("MENU_LEVEL"));
				menu.setAddBy(rs.getString("ADD_BY"));
				menu.setAddDate(rs.getString("ADD_DATE"));
				menu.setAddTime(rs.getString("ADD_TIME"));
				menu.setSortNum(rs.getString("SORT_NUM"));
				menu.setActiveFlag(rs.getString("ACTIVE_FLAG"));
				list.add(menu);
			}
		}catch(SQLException sqlE){
			sqlE.printStackTrace();
		}finally{
			DbPool.close(conn, pstmt, rs);
		}
		return list;
	}
	public static String getMenuResult(List<Menu> list, String path, String account) {
		// TODO Auto-generated method stub
		String result = "";
		for(Menu menu:list){
			result+="   <div class=\"accordion-heading\"> "+
                    "        <a class=\"accordion-toggle\" data-toggle=\"collapse\" data-parent=\"#accordion2\" href=\"#collapse"+menu.getMenuId()+"\"> " + 
                    "            <img class=\"left-icon\" src=\""+path+menu.getMenuImg()+"\" /><span class=\"left-title\">"+menu.getMenuName()+"</span> " +
                    "        </a> " +
                    "   </div> " +
                    "   <div id=\"collapse"+menu.getMenuId()+"\" class=\"accordion-body collapse in\"> ";
			
			List<Menu> secMenus = getMenuListByAccount(menu.getMenuId(),account);
			for(Menu menu_i:secMenus){
				result+="   <div class=\"accordion-inner\"> " +
                        "    	<a href=\""+path+menu_i.getMenuRequest()+"\" target='mainFrame'> " +
                        "        	<img class=\"left-icon-child\" src=\""+path+menu_i.getMenuImg()+"\" /><span class=\"left-body\">"+menu_i.getMenuName()+"</span> " +
                        "    	</a> " +
                        "   </div> ";
			}
			
			result += "</div>";
		}
		return result;
	}
}
